package com.admin.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.admin.bean.AdminSpBean;
import com.admin.bean.AdminSpCpBean;
import com.admin.bean.AdminSpCpSearchBean;
import com.admin.util.SqlUtil;


@Repository
public class AdminSpCpDao {

	
	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private QueryRunner queryRunner;
	
	
	public int adAdminSpCpBean(AdminSpCpBean bean) {
		String sql = "insert into adminspcp(cpid,spid,spcode,spcommand,spfee,spisp,spprovince) values(?,?,?,?,?,?,?)";
		return jdbcTemplate.update(sql, new Object[]{bean.getCpid(),bean.getSpid(),bean.getSpcode(),bean.getSpcommand(),bean.getSpfee(),bean.getSpisp(),bean.getSpprovince()});
	}
	
	public AdminSpCpBean findAdminSpCpBean(int id) {
		String sql = "select * from adminspcp where id = ?";
		try{			
			return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<AdminSpCpBean>(AdminSpCpBean.class), id);
		}catch(RuntimeException e){
			e.printStackTrace();
			return null;
		}
	}
	
	public int delSpCp(int id){
		final String sql = "delete from adminspcp where id = ?";
		try {
			return jdbcTemplate.update(sql, new Object[]{id});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	//修改CP的按指令扣量
	public int updateKouLiang(int kouliang,int id){
		final String sql = "update adminspcp set kouliang=? where id = ?";
		try {
			return jdbcTemplate.update(sql, new Object[]{kouliang,id});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public int delSpCpBySpId(int spid){
		final String sql = "delete from adminspcp where spid = ?";
		try {
			return jdbcTemplate.update(sql, new Object[]{spid});
		} catch (DataAccessException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	
	public List<AdminSpCpSearchBean> findListBySpCp(int start,int limit,String condition) throws SQLException{
		//StringBuilder sb = new StringBuilder("select sc.id as id, sc.spid as spid,sc.cpid as cpid,sp.spname as spname,sc.spcode as spcode,sc.spcommand as spfenpeicommand,sp.spcommand as spcommand,sp.spisp as spisp,sp.spstatus as spstatus,sp.spfee as spfee , sp.spprovince as spprovince from adminspcp as sc,adminsp as sp where sc.spid = sp.id");
		StringBuilder sb = new StringBuilder(condition + " and sc.spid = sp.id and sc.cpid = au.id");
		
		
		String sql = SqlUtil.getPageSqlForSqlServerAsc("sc.kouliang as kouliang, sc.id as id,sp.spname as spname, sc.spid as spid,sc.cpid as cpid,au.cpname as cpname,sp.spname as spname,sc.spcode as spcode,sc.spcommand as spfenpeicommand,sp.spcommand as spcommand,sp.spisp as spisp,sp.spstatus as spstatus,sp.spfee as spfee , sp.spprovince as spprovince", 
				"sc.id", "adminspcp as sc,adminsp as sp,adminuser as au", " sc.id ", sb.toString(), start, limit);
		
		return queryRunner.query(sql, new BeanListHandler<AdminSpCpSearchBean>(AdminSpCpSearchBean.class),new Object[]{});
	}
	
	public int findCountBySpCp(String condition) throws SQLException{
		
		
		String sql = "select count(0) from adminspcp where " + condition;
		
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<AdminSpCpBean> findListBySpId(int spid) throws SQLException{
		final String sql = "select * from adminspcp where spid = "+spid;
		return queryRunner.query(sql, new BeanListHandler<AdminSpCpBean>(AdminSpCpBean.class),new Object[]{});
	}
	
	
	public List<AdminSpCpBean> findListByCpId(int cpid) throws SQLException{
		final String sql = "select * from adminspcp where cpid = "+cpid;
		return queryRunner.query(sql, new BeanListHandler<AdminSpCpBean>(AdminSpCpBean.class),new Object[]{});
	}
	
	public List<AdminSpCpBean> findAll() throws SQLException{
		final String sql = "select * from adminspcp";
		return queryRunner.query(sql, new BeanListHandler<AdminSpCpBean>(AdminSpCpBean.class),new Object[]{});
	}
	
}
